package com.zxl.working.mapper.backend;

import com.zxl.working.entity.backend.*;
import com.zxl.working.entity.dto.SellerAmountData;
import com.zxl.working.entity.dto.SellerRechargeInfo;
import com.zxl.working.entity.dto.SellerTradeDetailsDto;
import org.apache.ibatis.annotations.*;
import org.springframework.beans.factory.annotation.Qualifier;

import java.util.List;

@Mapper
@Qualifier("backEndSqlSessionTemplate")
public interface SellerMapper {

    @Select("select * from seller_info where seller_Id = #{sellerId} limit 1")
    @Results({
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "importId",column = "import_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "sellerMobile",column = "seller_mobile"),
            @Result(property = "sellerCity",column = "seller_city"),
            @Result(property = "loadDate",column = "load_date"),
            @Result(property = "maxPay",column = "max_pay"),
            @Result(property = "minPay",column = "min_pay"),
            @Result(property = "taxPayerCode",column = "tax_payer_code"),
            @Result(property = "sellerAddress",column = "seller_address"),
            @Result(property = "sellerPhone",column = "seller_phone"),
            @Result(property = "sellerBank",column = "seller_bank"),
            @Result(property = "sellerBankAccount",column = "seller_bank_account"),
            @Result(property = "sellerState",column = "seller_state"),
            @Result(property = "loginId",column = "login_id"),
            @Result(property = "loginName",column = "login_name"),
            @Result(property = "creditCode",column = "credit_code"),
            @Result(property = "businessLicenseFile",column = "business_license_file"),
            @Result(property = "isAuthentication",column = "is_authentication"),
            @Result(property = "businessLicenseCode",column = "business_license_code"),
            @Result(property = "isOld",column = "is_old")
    })
    SellerInfo qrySellerInfoBySellerId(@Param("sellerId") String sellerId);

    @Select("SELECT s.* ,u.create_time FROM backend.seller_info s LEFT JOIN admin.sys_user u ON s.login_id = u.user_id  WHERE s.login_name = #{loginName}  AND s.seller_state = 'Y'")
    @Results({
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "importId",column = "import_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "sellerMobile",column = "seller_mobile"),
            @Result(property = "sellerCity",column = "seller_city"),
            @Result(property = "loadDate",column = "load_date"),
            @Result(property = "maxPay",column = "max_pay"),
            @Result(property = "minPay",column = "min_pay"),
            @Result(property = "taxPayerCode",column = "tax_payer_code"),
            @Result(property = "sellerAddress",column = "seller_address"),
            @Result(property = "sellerPhone",column = "seller_phone"),
            @Result(property = "sellerBank",column = "seller_bank"),
            @Result(property = "sellerBankAccount",column = "seller_bank_account"),
            @Result(property = "sellerState",column = "seller_state"),
            @Result(property = "loginId",column = "login_id"),
            @Result(property = "loginName",column = "login_name"),
            @Result(property = "creditCode",column = "credit_code"),
            @Result(property = "businessLicenseFile",column = "business_license_file"),
            @Result(property = "isAuthentication",column = "is_authentication"),
            @Result(property = "businessLicenseCode",column = "business_license_code"),
            @Result(property = "appKey",column = "app_key"),
            @Result(property = "appSecret",column = "app_secret"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "adoptIp",column = "adopt_ip"),
    })
    SellerInfo qrySellerInfoByLogin(@Param("loginName") String loginName,@Param("loginId") String loginId);

    @Select("select * from seller_upload_detail where seller_account_id = #{sellerId} order by create_time desc limit #{page},#{pageSize}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "investSerial",column = "invest_serial"),
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "investAmount",column = "invest_amount"),
            @Result(property = "serviceAmount",column = "service_amount"),
            @Result(property = "transer",column = "transer"),
            @Result(property = "comment",column = "comment"),
            @Result(property = "status",column = "status"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
    })
    List<SellerUploadDetail> qryDetails(@Param("sellerId") String sellerId,@Param("page") int page,@Param("pageSize")int pageSize);

    @Select("select count(1) from seller_upload_detail")
    int qryDetailsTotal();

    @Insert("insert into seller_upload_detail(id,invest_serial,seller_account_id,service_id,invest_amount,transer,comment,status,service_amount) " +
            "values(#{id},#{investSerial},#{sellerAccountId},#{serviceId},#{investAmount},#{transer},#{comment},#{status},#{serviceAmount})")
    void insUploadDetail(SellerUploadDetail sellerUploadDetail);

    @Select("select * from agent_seller_relation where seller_id = #{sellerId} and ref_state = 'Y' limit 1")
    @Results({
            @Result(property = "relationId",column = "relation_id"),
            @Result(property = "agentId",column = "agent_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "serverAmountPoint",column = "server_amount_point"),
            @Result(property = "refState",column = "ref_state"),
            @Result(property = "serviceFormula",column = "service_formula"),
    })
    AgentSellerRelation qrySellerR(@Param("sellerId")String sellerId);

    @Select("select a.*,b.seller_name,c.service_name,IFNULL(d.billTotal,0) as billTotal from seller_upload_detail a " +
            "left join seller_info b on a.seller_account_id = b.seller_id " +
            "left join service_info c on a.service_id = c.service_id " +
            "left join ( SELECT sum( taxable_total ) AS billTotal, seller_id, service_id, order_id FROM seller_bill WHERE `status` = 1 GROUP BY seller_id, service_id, order_id ) d on ( a.seller_account_id, a.service_id, a.id ) = ( d.seller_id, d.service_id, d.order_id ) " +
            "WHERE a.seller_account_id = #{sellerId} " +
            "ORDER BY a.create_time DESC " +
            "limit #{start},#{pageSize}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "investSerial",column = "invest_serial"),
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "investAmount",column = "invest_amount"),
            @Result(property = "serviceAmount",column = "service_amount"),
            @Result(property = "transer",column = "transer"),
            @Result(property = "comment",column = "comment"),
            @Result(property = "status",column = "status"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "billTotal",column = "billTotal")
    })
    List<SellerUploadDetail> qryInvoice(@Param("sellerId")String sellerId,@Param("start") int start,@Param("pageSize")int pageSize);

    @Select("select a.*,b.seller_name,c.service_name,IFNULL(d.billTotal,0) as billTotal from seller_upload_detail a " +
            "left join seller_info b on a.seller_account_id = b.seller_id " +
            "left join service_info c on a.service_id = c.service_id " +
            "left join ( SELECT sum( taxable_total ) AS billTotal, seller_id, service_id, order_id FROM seller_bill WHERE `status` = 1 GROUP BY seller_id, service_id, order_id ) d on ( a.seller_account_id, a.service_id, a.id ) = ( d.seller_id, d.service_id, d.order_id ) " +
            "WHERE a.seller_account_id = #{sellerId} and a.status = 1 " +
            "ORDER BY a.create_time DESC " +
            "limit #{start},#{pageSize}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "investSerial",column = "invest_serial"),
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "investAmount",column = "invest_amount"),
            @Result(property = "serviceAmount",column = "service_amount"),
            @Result(property = "transer",column = "transer"),
            @Result(property = "comment",column = "comment"),
            @Result(property = "status",column = "status"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "billTotal",column = "billTotal")
    })
    List<SellerUploadDetail> qrySuccInvoice(@Param("sellerId")String sellerId,@Param("start") int start,@Param("pageSize")int pageSize);

    @Select("select count(1) from seller_upload_detail where seller_account_id = #{sellerId}")
    int qryInvoiceCnt(@Param("sellerId")String sellerId);

    @Select("select count(1) from seller_upload_detail where seller_account_id = #{sellerId} and a.status = 1 ")
    int qryInvoiceSuccCnt(@Param("sellerId")String sellerId);

    @Select("select a.seller_id,a.seller_name,a.seller_company_name,c.service_name,c.service_com,c.service_code,c.service_bank,c.service_bank_name,c.service_account,a.max_pay,a.min_pay,d.server_amount_point " +
            "from seller_info a,service_seller_relation b,service_info c,agent_seller_relation d where a.seller_id = b.seller_id and b.service_id = c.service_id " +
            "and a.seller_id = d.seller_id and b.ref_state = 'Y' and a.seller_id = #{sellerId} and d.ref_state = 'Y' ")
    @Results({
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "sellerCompanyName",column = "seller_company_name"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "serviceComName",column = "service_com"),
            @Result(property = "enterpriseCode",column = "service_code"),
            @Result(property = "bankAccountName",column = "service_bank"),
            @Result(property = "bankName",column = "service_bank_name"),
            @Result(property = "bankCode",column = "service_account"),
            @Result(property = "maxPay",column = "max_pay"),
            @Result(property = "minPay",column = "min_pay"),
            @Result(property = "serverAmountPoint",column = "server_amount_point")
    })
    List<SellerRechargeInfo> rechargeInfos(String sellerId);

//        @Select("SELECT a.seller_account_id,a.service_id,a.seller_id, s.`seller_name`,i.`service_name`, (SELECT SUM(`trade_money`) FROM seller_account_detail b" +
//                " WHERE a.seller_id = b.seller_id" +
//                " AND b.order_source='05' or b.order_source='02') AS a1," +
//                " (SELECT SUM(`trade_money`) FROM seller_account_detail b" +
//                " WHERE a.seller_id = b.seller_id" +
//                " AND b.order_source='03') AS a2," +
//                " (SELECT SUM(`trade_money`) FROM seller_account_detail b" +
//                " WHERE a.seller_id = b.seller_id" +
//                " AND b.order_source='00')AS a3 FROM seller_account_detail a" +
//                " LEFT JOIN `seller_info` s ON s.`seller_id`=a.`seller_id`" +
//                " LEFT JOIN `service_info` i ON i.`service_id`=a.`service_id`" +
//                " WHERE a.seller_id= #{sellerId} " +
//                " LIMIT 1")
    @Select("SELECT rr.service_id,rr.seller_id,rr.seller_name,rr.service_name,rr.a1 AS a1,rr.a2 AS a2,rr.a3-rr.a4 AS a3,rr.a5,rr.a6 FROM " +
            " (SELECT service_id,seller_id,seller_name,service_name,SUM(r.a1) a1,SUM(r.a2) a2,SUM(r.a3) a3,SUM(r.a4) a4,SUM(r.a5) a5,SUM(r.a6) a6 FROM " +
            " (SELECT res.service_id,res.seller_id,s.`seller_name`,i.`service_name`,res.a1,res.a2,res.a3,res.a4,res.a5,res.a6  FROM " +
            " (SELECT seller_id,service_id,order_source, " +
            " SUM(IF(pay_subject='0003' AND pay_type=1,trade_money,0)) AS a1, " +
            " SUM(IF(pay_subject='0002' AND pay_type=1,trade_money,0)) AS a2, " +
            " SUM(IF(pay_subject='0001' AND pay_type=1,trade_money,0)) AS a3," +
            " SUM(IF(pay_subject='0004' AND pay_type=1,trade_money,0)) AS a4," +
            " SUM(IF(pay_subject='0008' AND pay_type=1,trade_money,0)) AS a5," +
            " SUM(IF(pay_subject='0009' AND pay_type=1,trade_money,0)) AS a6" +
            " FROM seller_account_detail " +
            " WHERE seller_id = #{sellerId} " +
            " GROUP BY service_id,order_source) AS res " +
            " LEFT JOIN `seller_info` s ON s.`seller_id`=res.`seller_id` " +
            " LEFT JOIN `service_info` i ON i.`service_id`=res.`service_id` " +
            " ) AS r " +
            " GROUP BY service_id,seller_id,seller_name,service_name) rr")
    @Results({
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "investTotal",column = "a1"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serverAmount",column = "a2"),
            @Result(property = "sendAmount",column = "a3"),
            @Result(property = "systemAdd",column = "a5"),
            @Result(property = "systemDel",column = "a6"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceName",column = "service_name")
    })
    List<SellerAccount> qryAccounts(@Param("sellerId") String sellerId);

    @Select("select service_type from seller_info where seller_id = #{sellerId}")
    String qryServiceTypeBySellerId(@Param("sellerId") String sellerId);

    @Select("select sum(a.real_amount) as totalAmount,sum(IF(a.order_type=1,a.real_amount,0)) as succAmount " +
            "from seller_provide_order a,seller_account_detail b where a.order_id = b.trade_serial and b.order_source = '00' and a.seller_id = #{sellerId}")
    @Results({
            @Result(property = "totalAmount",column = "totalAmount"),
            @Result(property = "succAmount",column = "succAmount")
    })
    SellerAmountData qryAmount(@Param("sellerId") String sellerId);

    @Select("select a.order_no,a.apply_time,a.company_name,a.seller_id,b.* from platform_grant a,seller_trade_details b where a.id = b.platform_grant_id and a.seller_id = #{sellerId}")
    @Results({
            @Result(property = "orderNo",column = "order_no"),
            @Result(property = "applyTime",column = "apply_time"),
            @Result(property = "sellerName",column = "company_name"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "id",column = "id"),
            @Result(property = "platformGrantId",column = "platform_grant_id"),
            @Result(property = "tradeNo",column = "trade_no"),
            @Result(property = "tradeUserName",column = "trade_user_name"),
            @Result(property = "tradeUserBankName",column = "trade_user_bank_name"),
            @Result(property = "tradeUserBankNo",column = "trade_user_bank_no"),
            @Result(property = "tradeUserMoney",column = "trade_user_money"),
            @Result(property = "tradeTime",column = "trade_time"),
            @Result(property = "tradeStatus",column = "trade_status"),
            @Result(property = "tradeOperatorUserId",column = "trade_operator_user_id"),
            @Result(property = "tradeRemark",column = "trade_remark"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time")
    })
    List<SellerTradeDetailsDto> qryTradeDetails(@Param("sellerId")String sellerId);

    @Select("select * from agent_account where agent_id = #{agentId} limit 1")
    @Results({
            @Result(property = "agentAccountId",column = "agent_account_id"),
            @Result(property = "agentAccount",column = "agent_account"),
            @Result(property = "agentId",column = "agent_id"),
            @Result(property = "agentAccountBalance",column = "agent_account_balance"),
            @Result(property = "agentAccountIncome",column = "agent_account_income"),
            @Result(property = "agentAccountPay",column = "agent_account_pay"),
            @Result(property = "agentAccountCommission",column = "agent_account_commission"),
            @Result(property = "agentAccountFreeze",column = "agent_account_freeze"),
            @Result(property = "stepAmountPoint",column = "step_amount_point"),
            @Result(property = "baseAmountPoint",column = "base_amount_point")
    })
    AgentAccount qryAgentAccount(@Param("agentId") String agentId);

    @Insert("insert into seller_provide_order_temp(`order_id`, `seller_id`, `seller_name`, `service_id`, `service_name`, `apply_time`, " +
            "`provide_list`, `provide_amount`, `real_amount`, `order_type`, `base_amount_point`, `over_amount_point`, `server_amount_point`, `step_amount_point`,`report_file`) " +
            "values(#{orderId},#{sellerId},#{sellerName},#{serviceId},#{serviceName},#{applyTime},#{provideList},#{provideAmount},#{realAmount},#{orderType},#{baseAmountPoint}," +
            "#{overAmountPoint},#{serverAmountPoint},#{stepAmountPoint},#{reportFile})")
    void insProvideOrderTemp(SellerProvideOrder sellerProvideOrder);

    @Insert("insert into provide_detail_temp(`id`,`fbalance_serial`, `order_id`, `trade_serial`, `ask_time`, `card_code`, `payee_name`, `payee_account`, `pay_amount`, " +
            "`payee_account_bank_name`, `payee_mobile`, `payee_remark`, `status`, `reason`) values(#{id},#{fbalanceSerial},#{orderId},#{tradeSerial},#{askTime},#{cardCode}," +
            "#{payeeName},#{payeeAccount},#{payAmount},#{payeeAccountBankName},#{payeeMobile},#{payeeRemark},#{status},#{reason})")
    void insProvideDetailsTemp(ProvideDetail provideDetail);

    @Update("update seller_account set account_total = account_total-${amount} where seller_id = #{sellerId}")
    void updSellerAccount(@Param("amount") Double amount,@Param("sellerId") String sellerId,@Param("serviceId")String serviceId);

    @Insert("insert into seller_provide_order select * from seller_provide_order_temp where order_id = #{orderId}")
    void insProvideOrder(@Param("orderId") String orderId);

    @Insert("insert into provide_detail select * from provide_detail_temp where order_id = #{orderId}")
    void insProvideDetails(@Param("orderId") String orderId);

    @Select("select sum(pay_amount) as amount from provide_detail where STATUS in(0,1) and card_code = #{cardCode} and DATE_FORMAT( ask_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )")
    Double qryAmountByCode(@Param("cardCode") String cardCode);

    @Select("select * from seller_provide_order_temp where order_id = #{orderId}")
    @Results({
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "applyTime",column = "apply_time"),
            @Result(property = "endTime",column = "end_time"),
            @Result(property = "provideList",column = "provide_list"),
            @Result(property = "provideAmount",column = "provide_amount"),
            @Result(property = "realAmount",column = "real_amount"),
            @Result(property = "orderType",column = "order_type"),
            @Result(property = "baseAmountPoint",column = "base_amount_point"),
            @Result(property = "overAmountPoint",column = "over_amount_point"),
            @Result(property = "serverAmountPoint",column = "server_amount_point"),
            @Result(property = "stepAmountPoint",column = "step_amount_point"),
            @Result(property = "overAmount",column = "over_amount"),
            @Result(property = "stepAmount",column = "step_amount"),
            @Result(property = "importId",column = "import_id"),
    })
    SellerProvideOrder qryOrder(@Param("orderId") String orderId);

    @Select("<script>" +
            "select * from seller_provide_order where seller_id = #{sellerId} " +
            "<when test='orderId!=null'>" +
            " and order_id = #{orderId}" +
            "</when>" +
            "<when test='interest!=null'>" +
            " and order_type = #{interest}" +
            "</when>" +
            " and apply_time &gt;=#{beginDate} and apply_time &lt;=#{endDate}" +
            " order by apply_time desc limit #{start},#{pageSize}" +
            "</script>")
    @Results({
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "applyTime",column = "apply_time"),
            @Result(property = "endTime",column = "end_time"),
            @Result(property = "provideList",column = "provide_list"),
            @Result(property = "provideAmount",column = "provide_amount"),
            @Result(property = "realAmount",column = "real_amount"),
            @Result(property = "failedAmount",column = "failed_amount"),
            @Result(property = "orderType",column = "order_type"),
            @Result(property = "baseAmountPoint",column = "base_amount_point"),
            @Result(property = "overAmountPoint",column = "over_amount_point"),
            @Result(property = "serverAmountPoint",column = "server_amount_point"),
            @Result(property = "stepAmountPoint",column = "step_amount_point"),
            @Result(property = "overAmount",column = "over_amount"),
            @Result(property = "stepAmount",column = "step_amount"),
            @Result(property = "importId",column = "import_id"),
            @Result(property = "approveStatus",column = "approve_status")
    })
    List<SellerProvideOrder> qryOrderBySeller(@Param("sellerId") String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize,
                                              @Param("beginDate") String beginDate,@Param("endDate") String endDate,@Param("orderId") String orderId,@Param("interest") Integer interest);

    @Select("<script>" +
            "select count(1) as cnt from seller_provide_order where seller_id = #{sellerId} " +
            "<when test='orderId!=null'>" +
            " and order_id = #{orderId}" +
            "</when>" +
            " and apply_time &gt;=#{beginDate} and apply_time &lt;=#{endDate}" +
            "<when test='interest!=null'>" +
            " and order_type = #{interest}" +
            "</when>" +
            "</script>")
    Integer qryOrderBySellerCnt(@Param("sellerId") String sellerId,@Param("beginDate") String beginDate,@Param("endDate") String endDate,@Param("orderId") String orderId,@Param("interest") Integer interest);

    @Select("<script>" +
            "select a.*,b.seller_id,b.seller_name,b.service_id,b.service_name,b.order_type as ostatus " +
            " from provide_detail a,seller_provide_order b where a.order_id = b.order_id  and b.seller_id = #{sellerId} " +
            "<when test='orderId!=null'>" +
            " and a.order_id = #{orderId}" +
            "</when>" +
            "<when test='name!=null'>" +
            " and a.payee_name = #{name}" +
            "</when>" +
            "<when test='account!=null'>" +
            " and a.payee_account = #{account}" +
            "</when>" +
            " and ask_time &gt;= #{beginDate} and ask_time &lt;= #{endDate}" +
            " order by ask_time desc,a.order_id limit #{start},#{pageSize}" +
            "</script>")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "fbalanceSerial",column = "fbalance_serial"),
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "tradeSerial",column = "trade_serial"),
            @Result(property = "askTime",column = "ask_time"),
            @Result(property = "cardCode",column = "card_code"),
            @Result(property = "payeeName",column = "payee_name"),
            @Result(property = "payeeAccount",column = "payee_account"),
            @Result(property = "payAmount",column = "pay_amount"),
            @Result(property = "payeeAccountBankName",column = "payee_account_bank_name"),
            @Result(property = "payeeMobile",column = "payee_mobile"),
            @Result(property = "payeeRemark",column = "payee_remark"),
            @Result(property = "status",column = "ostatus"),
            @Result(property = "reason",column = "reason"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "paymentStatus",column = "payment_status"),
            @Result(property = "paymentMsg",column = "payment_msg"),
            @Result(property = "paymentCode",column = "payment_code"),

    })
    List<ProvideDetail> qryProvideDetails(@Param("sellerId") String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize,
                                          @Param("beginDate") String beginDate,@Param("endDate") String endDate,@Param("orderId") String orderId,@Param("name") String name,@Param("account") String account);

    @Select("<script>" +
            "select count(1) as cnt " +
            "from provide_detail a,seller_provide_order b where a.order_id = b.order_id and b.seller_id = #{sellerId} " +
            "<when test='orderId!=null'>" +
            " and a.order_id = #{orderId}" +
            "</when>" +
            "<when test='name!=null'>" +
            " and a.payee_name = #{name}" +
            "</when>" +
            "<when test='account!=null'>" +
            " and a.payee_account = #{account}" +
            "</when>" +
            " and ask_time &gt;= #{beginDate} and ask_time &lt;= #{endDate}" +
            "</script>")
    Integer qryProvideDetailsCnt(@Param("sellerId") String sellerId,
                                 @Param("beginDate") String beginDate,@Param("endDate") String endDate,@Param("orderId") String orderId,@Param("name") String name,@Param("account") String account);

    @Select("select sum(real_amount-ifnull(failed_amount,0)) as totalSend from seller_provide_order where order_type = 1 and seller_id = #{sellerId}")
    Double qryTotalSend(@Param("sellerId") String sellerId);

    @Select("select sum(real_amount-ifnull(failed_amount,0)) as totalSend from seller_provide_order where order_type = 1 and DATE_FORMAT(apply_time,'%Y-%m') = DATE_FORMAT(NOW(),'%Y-%m') and seller_id = #{sellerId}")
    Double qrySuccTotalSend(@Param("sellerId") String sellerId);

    @Select("select a.*,IFNULL(d.billTotal,0) as billTotal,(a.provide_amount-a.real_amount) as serviceAmount from seller_provide_order a " +
            "left join ( SELECT sum( taxable_total ) AS billTotal, seller_id, service_id, order_id FROM seller_bill WHERE `status` in (0,1) GROUP BY seller_id, service_id, order_id ) d on ( a.seller_id, a.service_id, a.order_id ) = ( d.seller_id, d.service_id, d.order_id ) " +
            "WHERE a.seller_id = #{sellerId} and a.order_type = 1 " +
            "ORDER BY a.apply_time DESC " +
            "limit #{start},#{pageSize}")
    @Results({
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "applyTime",column = "apply_time"),
            @Result(property = "endTime",column = "end_time"),
            @Result(property = "provideList",column = "provide_list"),
            @Result(property = "provideAmount",column = "provide_amount"),
            @Result(property = "realAmount",column = "real_amount"),
            @Result(property = "orderType",column = "order_type"),
            @Result(property = "baseAmountPoint",column = "base_amount_point"),
            @Result(property = "overAmountPoint",column = "over_amount_point"),
            @Result(property = "serverAmountPoint",column = "server_amount_point"),
            @Result(property = "stepAmountPoint",column = "step_amount_point"),
            @Result(property = "overAmount",column = "over_amount"),
            @Result(property = "stepAmount",column = "step_amount"),
            @Result(property = "importId",column = "import_id"),
            @Result(property = "billTotal",column = "billTotal"),
            @Result(property = "serviceAmount",column = "serviceAmount"),
    })
    List<SellerProvideOrder> qryNeedBill(@Param("sellerId")String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize);

    @Select("SELECT COUNT(*) FROM seller_upload_detail a  " +
            " WHERE a.status = 1 and a.seller_account_id = #{sellerId} ")
    Integer qryNeedBillCnt(@Param("sellerId") String sellerId);


    @Select("select order_id from seller_provide_order_temp where order_id like #{orderId} order by order_id desc limit 1")
    String qryLastOrderIdInF(@Param("orderId")String orderId);


    @Select("select id from provide_detail_temp where id like #{orderId} order by id desc limit 1")
    String qryLastOrderIdInL(@Param("orderId")String orderId);

    @Select("select id from seller_upload_detail where id like #{orderId} order by id desc limit 1")
    String qryLastOrderIdInC(@Param("orderId")String orderId);

    @Update("UPDATE provide_detail_temp SET status=2 WHERE order_id=#{orderId}")
    int updateProvideDetailTemp_status(@Param("orderId")String orderId);

    @Update("UPDATE provide_detail SET status=2 WHERE order_id=#{orderId}")
    int updateProvideDetail_status(@Param("orderId")String orderId);


    @Update("UPDATE seller_provide_order SET order_type=2 WHERE order_id=#{orderId}")
    int updateSellerProvideOrder_status(@Param("orderId")String orderId);

    @Update("UPDATE seller_provide_order_temp SET order_type=2 WHERE order_id=#{orderId}")
    int updateSellerProvideOrderTemp_status(@Param("orderId")String orderId);

    @Select("SELECT a.*,IFNULL(d.billTotal,0) AS billTotal,c.service_name, b.seller_name FROM seller_upload_detail a " +
            "LEFT JOIN ( SELECT SUM( taxable_total ) AS billTotal, upload_id FROM seller_bill WHERE `status` IN (0,1) GROUP BY  upload_id ) d " +
            "ON a.id  = d.upload_id " +
            " LEFT JOIN service_info c " +
            " ON  a.service_id = c.service_id  " +
            " LEFT join seller_info b " +
            " ON a.seller_account_id = b.seller_id " +
            "WHERE a.seller_account_id = #{sellerId} and a.status = 1 " +
            "ORDER BY a.create_time DESC " +
            "limit #{start},#{pageSize}")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "investSerial",column = "invest_serial"),
            @Result(property = "sellerAccountId",column = "seller_account_id"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "investAmount",column = "invest_amount"),
            @Result(property = "serviceAmount",column = "service_amount"),
            @Result(property = "transer",column = "transer"),
            @Result(property = "comment",column = "comment"),
            @Result(property = "status",column = "status"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "billTotal",column = "billTotal")
    })
    List<SellerUploadDetail> qryCanProvide(@Param("sellerId")String sellerId,@Param("start") Integer start,@Param("pageSize") Integer pageSize);
    
    @Select("select account_total from seller_account where seller_id = #{sellerId}")
    String findByMoney(@Param("sellerId")String sellerId);

    @Select("select real_amount from seller_provide_order_temp where order_id = #{orderId}")
	String selectByMoney(@Param("orderId")String orderId);
    
    
    
    @Select("<script>" +
            "select a.*,b.seller_id,b.seller_name,b.service_id,b.service_name,b.order_type as ostatus " +
            " from provide_detail a,seller_provide_order b where a.order_id = b.order_id  and b.seller_id = #{sellerId} " +
            "<when test='orderId!=null'>" +
            " and a.order_id = #{orderId}" +
            "</when>" +
            "<when test='name!=null'>" +
            " and a.payee_name = #{name}" +
            "</when>" +
            "<when test='account!=null'>" +
            " and a.payee_account = #{account}" +
            "</when>" +
            " and ask_time &gt;= #{beginDate} and ask_time &lt;= #{endDate}" +
            " order by ask_time desc,a.order_id" +
            "</script>")
    @Results({
            @Result(property = "id",column = "id"),
            @Result(property = "fbalanceSerial",column = "fbalance_serial"),
            @Result(property = "orderId",column = "order_id"),
            @Result(property = "tradeSerial",column = "trade_serial"),
            @Result(property = "askTime",column = "ask_time"),
            @Result(property = "cardCode",column = "card_code"),
            @Result(property = "payeeName",column = "payee_name"),
            @Result(property = "payeeAccount",column = "payee_account"),
            @Result(property = "payAmount",column = "pay_amount"),
            @Result(property = "payeeAccountBankName",column = "payee_account_bank_name"),
            @Result(property = "payeeMobile",column = "payee_mobile"),
            @Result(property = "payeeRemark",column = "payee_remark"),
            @Result(property = "status",column = "ostatus"),
            @Result(property = "reason",column = "reason"),
            @Result(property = "createTime",column = "create_time"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "sellerId",column = "seller_id"),
            @Result(property = "sellerName",column = "seller_name"),
            @Result(property = "serviceId",column = "service_id"),
            @Result(property = "serviceName",column = "service_name"),
            @Result(property = "paymentStatus",column = "payment_status"),
            @Result(property = "paymentMsg",column = "payment_msg"),
            @Result(property = "paymentCode",column = "payment_code"),

    })
    List<ProvideDetail> qryProvideDetailsExport(@Param("sellerId") String sellerId,@Param("beginDate")String beginDate,@Param("endDate")String endDate,@Param("orderId")String orderId,@Param("account")String account,@Param("name")String name);


}
